import json
import xlrd

data = xlrd.open_workbook(r"C:\Users\ZH\Desktop\1.xlsx")
sheet = data.sheet_by_index(0)
col_values = sheet.col_values(4)
#col_2=sheet.col_values(7)
nrows = sheet.nrows
jsondata={
    "data_wordCloud":[]
    }

def serch(item,key):
    isfind=False
    for i in range(len(item)):
        if(item[i]["name"]==key):
            isfind=True
            return i
    if(isfind==False):
        return -1



    
last=" "
num=0
counter=-1;
for item in col_values:
   if(item==last):
       num+=1
       continue
   else:
       #if(num>=3):    
       jsondata["data_wordCloud"].append({"name":last,"value":num})
       last=item
       num=1
jsondata["data_wordCloud"].append({"name":last,"value":num})



#for i in range(nrows):
#    row=sheet.row_values(i, start_colx=0, end_colx=None)
#    ind=row[4]
#    sex=row[0]
#    pos=row[3]
#    comp=row[1]
#    chara=row[2]
#    index=serch(jsondata["children"],ind)
#    if(index==-1):
#        jsondata["children"].append(
#            {
#                "value":0,
#                "name":ind,
#                "data_sexchart":[
#                        {
#                        "name":"男",
#                        "value":0,
#                        },
#                        {
#                        "name":"女",
#                        "value":0,
#                        }
#                    ],
#                "data_pie": [
#                    {
#              "value": 0,
#              "name": "中初教育单位"
#            },
#            {
#              "value": 0,
#              "name": "国有企业"
#            },
#            {
#              "value": 0,
#              "name": "民营企业"
#            },
#            {
#              "value": 0,
#              "name": "党政机关"
#            },
#            {
#              "value": 0,
#              "name": "三资企业"
#            },
#            {
#              "value": 0,
#              "name": "部队"
#            },
#            {
#              "value": 0,
#              "name": "其它"
#            }
#              ],
#                 "data_map": [
#    {
#      "name": "北京",
#      "value": 0
#    },
#    {
#      "name": "天津",
#      "value": 0
#    },
#    {
#      "name": "上海",
#      "value": 0
#    },
#    {
#      "name": "重庆",
#      "value": 0
#    },
#    {
#      "name": "河北",
#      "value": 0
#    },
#    {
#      "name": "河南",
#      "value": 0
#    },
#    {
#      "name": "云南",
#      "value": 0
#    },
#    {
#      "name": "辽宁",
#      "value": 0
#    },
#    {
#      "name": "黑龙江",
#      "value": 0
#    },
#    {
#      "name": "湖南",
#      "value": 0
#    },
#    {
#      "name": "安徽",
#      "value": 0
#    },
#    {
#      "name": "山东",
#      "value": 0
#    },
#    {
#      "name": "新疆",
#      "value": 0
#    },
#    {
#      "name": "江苏",
#      "value": 0
#    },
#    {
#      "name": "浙江",
#      "value": 0
#    },
#    {
#      "name": "江西",
#      "value": 0
#    },
#    {
#      "name": "湖北",
#      "value": 0
#    },
#    {
#      "name": "广西",
#      "value":0
#    },
#    {
#      "name": "甘肃",
#      "value": 0
#    },
#    {
#      "name": "山西",
#      "value": 0
#    },
#    {
#      "name": "内蒙古",
#      "value": 0
#    },
#    {
#      "name": "陕西",
#      "value": 0
#    },
#    {
#      "name": "吉林",
#      "value": 0
#    },
#    {
#      "name": "福建",
#      "value": 0
#    },
#    {
#      "name": "贵州",
#      "value": 0
#    },
#    {
#      "name": "广东",
#      "value": 0
#    },
#    {
#      "name": "青海",
#      "value": 0
#    },
#    {
#      "name": "西藏",
#      "value": 0
#    },
#    {
#      "name": "四川",
#      "value": 0
#    },
#    {
#      "name": "宁夏",
#      "value":0
#    },
#    {
#      "name": "海南",
#      "value": 0
#    },
#    {
#      "name": "台湾",
#      "value": 0
#    },
#    {
#      "name": "香港",
#      "value": 0
#    },
#    {
#      "name": "澳门",
#      "value": 0
#    }
#  ],
#                 "data_wordCloud": []
#                }
#            )
#        index=serch(jsondata["children"],ind)
#    jsondata["children"][index]["value"]=jsondata["children"][index]["value"]+1
#    if(sex=='男'):
#        jsondata["children"][index]["data_sexchart"][0]["value"]=jsondata["children"][index]["data_sexchart"][0]["value"]+1
#    else:
#        jsondata["children"][index]["data_sexchart"][1]["value"]=jsondata["children"][index]["data_sexchart"][1]["value"]+1
#    chara_index=serch(jsondata["children"][index]["data_pie"],chara)
#    jsondata["children"][index]["data_pie"][chara_index]["value"]=jsondata["children"][index]["data_pie"][chara_index]["value"]+1
#    pos_index=serch(jsondata["children"][index]["data_map"],pos)
#    jsondata["children"][index]["data_map"][pos_index]["value"]=jsondata["children"][index]["data_map"][pos_index]["value"]+1
#    comp_index=serch(jsondata["children"][index]["data_wordCloud"],comp)
#    if(comp_index==-1):
#        jsondata["children"][index]["data_wordCloud"].append({
#            "name":comp,
#            "value":0
#            })
#        comp_index=serch(jsondata["children"][index]["data_wordCloud"],comp)
#    jsondata["children"][index]["data_wordCloud"][comp_index]["value"]=jsondata["children"][index]["data_wordCloud"][comp_index]["value"]+1


#for i in range(len(col_2)):
#    if(lastIndustry==col_2[i]):
#        if(lastcity==col_values[i]):
#            num+=1
#        else:
#            jsondata["positionOfIndustry"][counter][lastcity]=num
#            lastcity=col_values[i]
#            num=1
#    else:
#        jsondata["positionOfIndustry"].append({"industry":col_2[i]})
#        if(counter!=-1):
#            jsondata["positionOfIndustry"][counter][lastcity]=num
#        counter+=1
#        lastIndustry=col_2[i]
#        lastcity=col_values[i]
#        num=1

#jsondata["positionOfIndustry"][counter][lastcity]=num


with open(r"C:\Users\ZH\Desktop\1.txt", 'w') as f:
    file=json.dump(jsondata,f,ensure_ascii=False)
    